package com.st.lotte.dao;


import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import com.st.lotte.model.TLO_DELY_TRCE_RECV_IF;
import com.st.lotte.util.DBUtil;
import com.st.lotte.util.Database;
import com.st.lotte.util.LogTrace;

public class TLO_DELY_TRCE_RECV_IF_DAO {
	private static final Logger log = LoggerFactory.getLogger(TLO_DELY_TRCE_RECV_IF_DAO.class);
	
	Connection conn = null;
	Database db = new Database();
	
	public TLO_DELY_TRCE_RECV_IF_DAO(){
		conn = db.getConnection();
	}
	
	protected synchronized void finalize() throws Throwable {
		conn.close();
	}; 
	
	//당일 완료 
	public synchronized Vector<TLO_DELY_TRCE_RECV_IF> selectDayComplete(String date) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
		try {
			String sql = DBUtil.DAY_COMPLETE;
			log.debug("[select all query - dayComplete ] : "+sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, date);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TLO_DELY_TRCE_RECV_IF dtoTRCE = new TLO_DELY_TRCE_RECV_IF();
				dtoTRCE.setRECV_DATE(rs.getDate("RECV_DATE"));
				dtoTRCE.setSEQ(rs.getInt("SEQ"));
				dtoTRCE.setCMBN_ORD_NO(rs.getString("CMBN_ORD_NO"));
				dtoTRCE.setHODECO_CODE(rs.getString("HODECO_CODE"));
				dtoTRCE.setTRCE_STAT_CODE(rs.getString("TRCE_STAT_CODE"));
				dtoTRCE.setSLIP_NO(rs.getString("SLIP_NO"));
				dtoTRCE.setTRCE_STAT_DIV_CODE(rs.getString("TRCE_STAT_DIV_CODE"));
				dtoTRCE.setTRCE_STAT_PROC_DT(rs.getTimestamp("TRCE_STAT_PROC_DT"));
				dtoTRCE.setRMK4(rs.getString("RMK4"));
				list.add(dtoTRCE);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[RECV_START_DATE_END_DATE] Complete!!");
		
		return list;
	}	
	
	
	//전일 배송 완료 
	public synchronized Vector<TLO_DELY_TRCE_RECV_IF> selectBeforeComplete(String date) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
		try {
			String sql = DBUtil.BEFORE_COMPLETE;
			log.debug("[select all query - beforeComplete ] : "+sql + " "+date);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, date);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				TLO_DELY_TRCE_RECV_IF dtoTRCE = new TLO_DELY_TRCE_RECV_IF();
				dtoTRCE.setRECV_DATE(rs.getDate("RECV_DATE"));
				dtoTRCE.setSEQ(rs.getInt("SEQ"));
				dtoTRCE.setCMBN_ORD_NO(rs.getString("CMBN_ORD_NO"));
				dtoTRCE.setHODECO_CODE(rs.getString("HODECO_CODE"));
				dtoTRCE.setTRCE_STAT_CODE(rs.getString("TRCE_STAT_CODE"));
				dtoTRCE.setSLIP_NO(rs.getString("SLIP_NO"));
				dtoTRCE.setTRCE_STAT_DIV_CODE(rs.getString("TRCE_STAT_DIV_CODE"));
				dtoTRCE.setTRCE_STAT_PROC_DT(rs.getTimestamp("TRCE_STAT_PROC_DT"));
				dtoTRCE.setRMK4(rs.getString("RMK4"));
				list.add(dtoTRCE);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[RECV_START_DATE_END_DATE] Complete!!");
		
		return list;
	}
	
	//당일 배송 출발 
	public synchronized Vector<TLO_DELY_TRCE_RECV_IF> selectDayStart(String date) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
		try {
			String sql = DBUtil.DAY_START;
			log.debug("[select all query - dayStart ] : "+sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, date);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TLO_DELY_TRCE_RECV_IF dtoTRCE = new TLO_DELY_TRCE_RECV_IF();
				dtoTRCE.setRECV_DATE(rs.getDate("RECV_DATE"));
				dtoTRCE.setSEQ(rs.getInt("SEQ"));
				dtoTRCE.setCMBN_ORD_NO(rs.getString("CMBN_ORD_NO"));
				dtoTRCE.setHODECO_CODE(rs.getString("HODECO_CODE"));
				dtoTRCE.setTRCE_STAT_CODE(rs.getString("TRCE_STAT_CODE"));
				dtoTRCE.setSLIP_NO(rs.getString("SLIP_NO"));
				dtoTRCE.setTRCE_STAT_DIV_CODE(rs.getString("TRCE_STAT_DIV_CODE"));
				dtoTRCE.setTRCE_STAT_PROC_DT(rs.getTimestamp("TRCE_STAT_PROC_DT"));
				dtoTRCE.setRMK4(rs.getString("RMK4"));
				list.add(dtoTRCE);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[RECV_START_DATE_END_DATE] Complete!!");
		
		return list;
	}	
	
	
	//전일 배송 출발 
		public synchronized Vector<TLO_DELY_TRCE_RECV_IF> selectBeforeStart(String date) {
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
			try {
				String sql = DBUtil.BEFORE_START;
				log.debug("[select all query - beforeStart ] : "+sql);
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, date);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					TLO_DELY_TRCE_RECV_IF dtoTRCE = new TLO_DELY_TRCE_RECV_IF();
					dtoTRCE.setRECV_DATE(rs.getDate("RECV_DATE"));
					dtoTRCE.setSEQ(rs.getInt("SEQ"));
					dtoTRCE.setCMBN_ORD_NO(rs.getString("CMBN_ORD_NO"));
					dtoTRCE.setHODECO_CODE(rs.getString("HODECO_CODE"));
					dtoTRCE.setTRCE_STAT_CODE(rs.getString("TRCE_STAT_CODE"));
					dtoTRCE.setSLIP_NO(rs.getString("SLIP_NO"));
					dtoTRCE.setTRCE_STAT_DIV_CODE(rs.getString("TRCE_STAT_DIV_CODE"));
					dtoTRCE.setTRCE_STAT_PROC_DT(rs.getTimestamp("TRCE_STAT_PROC_DT"));
					dtoTRCE.setRMK4(rs.getString("RMK4"));
					list.add(dtoTRCE);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			LogTrace.TraceLog("[RECV_START_DATE_END_DATE] Complete!!");
			
			return list;
		}
		
		// 전체 데이터 카운트  
		public synchronized int selectALLRecvDataCNT() {
			int count = 0;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
			try {
				String sql = DBUtil.RECV_CNT ;
				log.debug("[select all query count] : "+sql);
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				TLO_DELY_TRCE_RECV_IF dtoTRCE = new TLO_DELY_TRCE_RECV_IF();
				while (rs.next()) {
					dtoTRCE.setCNT(rs.getInt("count"));
				}
			    count = dtoTRCE.getCNT();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			LogTrace.TraceLog("[RECV_DATA_COUNT] Complete!!");
			
			return count;
		}				
		
		// 전체 검색  
		public synchronized Vector<TLO_DELY_TRCE_RECV_IF> selectALLRecvData(String str, String gubun) {
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
			try {
				String sql = DBUtil.RECV_SELALL+" "+str ;
				log.debug("[select all query] : "+sql);
				pstmt = conn.prepareStatement(sql);
				
				
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					TLO_DELY_TRCE_RECV_IF dtoTRCE = new TLO_DELY_TRCE_RECV_IF();
					dtoTRCE.setRECV_DATE(rs.getDate("RECV_DATE"));
					dtoTRCE.setCMBN_ORD_NO(rs.getString("CMBN_ORD_NO"));
					dtoTRCE.setSEQ(rs.getInt("SEQ"));
					dtoTRCE.setHODECO_CODE(rs.getString("HODECO_CODE"));
					dtoTRCE.setTRCE_STAT_CODE(rs.getString("TRCE_STAT_CODE"));
					dtoTRCE.setORD_NO(rs.getString("ORD_NO"));
					dtoTRCE.setORD_GOODS_SEQ(rs.getString("ORD_GOODS_SEQ"));
					dtoTRCE.setORD_GOODS_DTL_SEQ(rs.getString("ORD_GOODS_DTL_SEQ"));
					dtoTRCE.setORD_GOODS_DELY_SEQ(rs.getString("ORD_GOODS_DELY_SEQ"));
					dtoTRCE.setORD_DTL_SN(rs.getString("ORD_DTL_SN"));
					dtoTRCE.setSLIP_NO(rs.getString("SLIP_NO"));
					dtoTRCE.setSYS_BASE_CODE(rs.getString("SYS_BASE_CODE"));
					dtoTRCE.setTRCE_STAT_DIV_CODE(rs.getString("TRCE_STAT_DIV_CODE"));
					dtoTRCE.setTRCE_STAT_PROC_DT(rs.getTimestamp("TRCE_STAT_PROC_DT"));
					dtoTRCE.setRMK1(rs.getString("RMK1"));
					dtoTRCE.setRMK2(rs.getString("RMK2"));
					dtoTRCE.setRMK3(rs.getString("RMK3"));
					dtoTRCE.setRMK4(rs.getString("RMK4"));
					dtoTRCE.setRMK5(rs.getString("RMK5"));
					dtoTRCE.setRMK6(rs.getString("RMK6"));
					dtoTRCE.setRMK7(rs.getString("RMK7"));
					dtoTRCE.setRMK8(rs.getString("RMK8"));
					dtoTRCE.setRMK9(rs.getString("RMK9"));	
					dtoTRCE.setRMK10(rs.getString("RMK10"));
					dtoTRCE.setINTF_STAT_CODE(rs.getString("INTF_STAT_CODE"));
					dtoTRCE.setINTF_ERR_CNTT(rs.getString("INTF_ERR_CNTT"));
					dtoTRCE.setINTF_DT(rs.getDate("INTF_DT"));
					dtoTRCE.setCHGMN_ID(rs.getString("CHGMN_ID"));
					dtoTRCE.setCHG_DT(rs.getDate("CHG_DT"));
					list.add(dtoTRCE);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			LogTrace.TraceLog("[RECV_START_DATE_END_DATE] Complete!!");
			
			return list;
		}		
		
		// 발송지연 검색 
		public synchronized int selectNotDeliveryDataCNT() {
			int count = 0;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
			try {
				String sql = DBUtil.NON_DELIVERY_CNT;
				log.debug("[select all query] : "+sql);
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					TLO_DELY_TRCE_RECV_IF dtoTRCE = new TLO_DELY_TRCE_RECV_IF();
					dtoTRCE.setCNT(rs.getInt("count"));
					count = dtoTRCE.getCNT();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			LogTrace.TraceLog("[RECV_START_DATE_END_DATE] Complete!!");
			
			return count;
		}		
		
	
	// 발송지연 검색 
	public synchronized Vector<TLO_DELY_TRCE_RECV_IF> selectNotDeliveryData(String str, String gubun) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector<TLO_DELY_TRCE_RECV_IF> list = new Vector<TLO_DELY_TRCE_RECV_IF>();
		try {
			String sql = DBUtil.NON_DELIVERY_QUERY+" "+str;
			log.debug("[select all query] : "+sql);
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TLO_DELY_TRCE_RECV_IF dtoTRCE = new TLO_DELY_TRCE_RECV_IF();
				dtoTRCE.setCOMPANY_CODE(rs.getString("COMPANY_CODE"));
				dtoTRCE.setRECV_DATE(rs.getDate("RECV_DATE"));
				dtoTRCE.setCMBN_ORD_NO(rs.getString("CMBN_ORD_NO"));
				dtoTRCE.setSEQ(rs.getInt("SEQ"));
				dtoTRCE.setHODECO_CODE(rs.getString("HODECO_CODE"));
				dtoTRCE.setTRCE_STAT_CODE(rs.getString("TRCE_STAT_CODE"));
				dtoTRCE.setORD_NO(rs.getString("ORD_NO"));
				dtoTRCE.setORD_GOODS_SEQ(rs.getString("ORD_GOODS_SEQ"));
				dtoTRCE.setORD_GOODS_DTL_SEQ(rs.getString("ORD_GOODS_DTL_SEQ"));
				dtoTRCE.setORD_GOODS_DELY_SEQ(rs.getString("ORD_GOODS_DELY_SEQ"));
				dtoTRCE.setORD_DTL_SN(rs.getString("ORD_DTL_SN"));
				dtoTRCE.setSLIP_NO(rs.getString("SLIP_NO"));
				dtoTRCE.setSYS_BASE_CODE(rs.getString("SYS_BASE_CODE"));
				dtoTRCE.setTRCE_STAT_DIV_CODE(rs.getString("TRCE_STAT_DIV_CODE"));
				dtoTRCE.setTRCE_STAT_PROC_DT(rs.getTimestamp("TRCE_STAT_PROC_DT"));
				dtoTRCE.setRMK1(rs.getString("RMK1"));
				dtoTRCE.setRMK2(rs.getString("RMK2"));
				dtoTRCE.setRMK3(rs.getString("RMK3"));
				dtoTRCE.setRMK4(rs.getString("RMK4"));
				dtoTRCE.setRMK5(rs.getString("RMK5"));
				dtoTRCE.setRMK6(rs.getString("RMK6"));
				dtoTRCE.setRMK7(rs.getString("RMK7"));
				dtoTRCE.setRMK8(rs.getString("RMK8"));
				dtoTRCE.setRMK9(rs.getString("RMK9"));	
				dtoTRCE.setRMK10(rs.getString("RMK10"));
				dtoTRCE.setINTF_STAT_CODE(rs.getString("INTF_STAT_CODE"));
				dtoTRCE.setINTF_ERR_CNTT(rs.getString("INTF_ERR_CNTT"));
				dtoTRCE.setINTF_DT(rs.getDate("INTF_DT"));
				dtoTRCE.setCHGMN_ID(rs.getString("CHGMN_ID"));
				dtoTRCE.setCHG_DT(rs.getDate("CHG_DT"));
				list.add(dtoTRCE);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("[RECV_START_DATE_END_DATE] Complete!!");
		
		return list;
	}
	
	
	public synchronized TLO_DELY_TRCE_RECV_IF selectALL_TLO_DELY_TRCE_RECV_IF_DAO(String slip_no) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		TLO_DELY_TRCE_RECV_IF dtoTRCE = new TLO_DELY_TRCE_RECV_IF();
		ArrayList<TLO_DELY_TRCE_RECV_IF> list = new ArrayList<TLO_DELY_TRCE_RECV_IF>();
		try {
			String sql = DBUtil.RECV_SELECTALL;
			pstmt.setString(1, slip_no);
			rs = pstmt.executeQuery();
			rs.next();
			dtoTRCE.setCOMPANY_CODE(rs.getString("COMPANY_CODE"));
			dtoTRCE.setRECV_DATE(rs.getDate("TRMN_DATE"));
			dtoTRCE.setCMBN_ORD_NO(rs.getString("CMBN_ORD_NO"));
			dtoTRCE.setSEQ(rs.getInt("SEQ"));
			dtoTRCE.setHODECO_CODE(rs.getString("HODECO_CODE"));
			dtoTRCE.setORD_NO(rs.getString("ORD_NO"));
			dtoTRCE.setORD_GOODS_SEQ(rs.getString("ORD_GOODS_SEQ"));
			dtoTRCE.setORD_GOODS_DTL_SEQ(rs.getString("ORD_GOODS_DTL_SEQ"));
			dtoTRCE.setORD_GOODS_DELY_SEQ(rs.getString("ORD_GOODS_DELY_SEQ"));
			dtoTRCE.setORD_DTL_SN(rs.getString("ORD_DTL_SN"));
			dtoTRCE.setSLIP_NO(rs.getString("SLIP_NO"));
			dtoTRCE.setSYS_BASE_CODE(rs.getString("SYS_BASE_CODE"));
			dtoTRCE.setRMK1(rs.getString("RMK1"));
			dtoTRCE.setRMK2(rs.getString("RMK2"));
			dtoTRCE.setRMK3(rs.getString("RMK3"));
			dtoTRCE.setRMK4(rs.getString("RMK4"));
			dtoTRCE.setRMK5(rs.getString("RMK5"));
			dtoTRCE.setRMK6(rs.getString("RMK6"));
			dtoTRCE.setRMK7(rs.getString("RMK7"));
			dtoTRCE.setRMK8(rs.getString("RMK8"));
			dtoTRCE.setRMK9(rs.getString("RMK9"));	
			dtoTRCE.setRMK10(rs.getString("RMK10"));
			dtoTRCE.setINTF_STAT_CODE(rs.getString("INTF_STAT_CODE"));
			dtoTRCE.setINTF_ERR_CNTT(rs.getString("INTF_ERR_CNTT"));
			dtoTRCE.setINTF_DT(rs.getDate("INTF_DT"));
			dtoTRCE.setCHGMN_ID(rs.getString("CHGMN_ID"));
			dtoTRCE.setCHG_DT(rs.getDate("CHG_DT"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("TRMN_OK_select");
		
		return dtoTRCE;
	}
	
	
	public synchronized void insertTLO_DELY_TRCE_RECV_IF_DAO(TLO_DELY_TRCE_RECV_IF dto) {
		PreparedStatement pstmt = null;
		try {
			String sql = DBUtil.INSERT;
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setString(i++, dto.getCOMPANY_CODE());
			pstmt.setString(i++, dto.getCMBN_ORD_NO());
			pstmt.setInt(i++, dto.getSEQ());
			pstmt.setString(i++, dto.getHODECO_CODE());
			pstmt.setString(i++, dto.getRECV_BIZS_DIV_CODE());
			pstmt.setString(i++, dto.getTRCE_STAT_CODE()); //5
			pstmt.setString(i++, dto.getORD_NO());
			pstmt.setString(i++, dto.getORD_GOODS_SEQ());
			pstmt.setString(i++, dto.getORD_GOODS_DTL_SEQ());
			pstmt.setString(i++, dto.getORD_GOODS_DELY_SEQ());
			pstmt.setString(i++, dto.getORD_DTL_SN()); // 10
			pstmt.setString(i++, dto.getSLIP_NO());
			pstmt.setString(i++, dto.getSYS_BASE_CODE());
			pstmt.setString(i++, dto.getLOCA_TELNO());
			pstmt.setString(i++, dto.getSTAFF_TELNO());
			pstmt.setString(i++, dto.getHODECO_DTPL_CNTT()); // 15
			pstmt.setString(i++, dto.getHODECO_ARRIPL_CNTT());
			pstmt.setString(i++, dto.getTRCE_STAT_DIV_CODE());
			pstmt.setTimestamp(i++,dto.getTRCE_STAT_PROC_DT());
			pstmt.setString(i++, dto.getRMK1());
			pstmt.setString(i++, dto.getRMK2());
			pstmt.setString(i++, dto.getRMK3()); //20
			pstmt.setString(i++, dto.getRMK4());
			pstmt.setString(i++, dto.getRMK5());
			pstmt.setString(i++, dto.getRMK6());
			pstmt.setString(i++, dto.getRMK7());
			pstmt.setString(i++, dto.getRMK8()); //25
			pstmt.setString(i++, dto.getRMK9());	
			pstmt.setString(i++, dto.getRMK10());
			pstmt.setString(i++, dto.getINTF_STAT_CODE());
			pstmt.setString(i++, dto.getINTF_ERR_CNTT());
			pstmt.setString(i++, dto.getCHGMN_ID()); // 30
			pstmt.executeUpdate();
		}catch(MySQLIntegrityConstraintViolationException e1){ 
			LogTrace.TraceLog(" already key : "+dto.getCOMPANY_CODE()+"/"+dto.getCMBN_ORD_NO()+"/"+ dto.getSEQ()+"/"+dto.getHODECO_CODE()+dto.getINTF_DT());
		}catch (SQLException e) {
			e.printStackTrace();
		}
		LogTrace.TraceLog("TLO_DELY_TRCE_RECV_insert complete! ");
	}

	public synchronized int detailCheck(String slip_no, String cmbn_ord_no, Timestamp trce_stat_proc_dt, String kind) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int count=0;
		
		TLO_DELY_TRCE_RECV_IF dto = new TLO_DELY_TRCE_RECV_IF();
		System.out.println("slip_no=="+slip_no+" cmbn_ord_no=="+cmbn_ord_no);
		try {
			String selectTableSQL = "select count(*) as count from TLO_DELY_TRCE_RECV_IF where slip_no='"+slip_no+"' and CMBN_ORD_NO ='"+cmbn_ord_no+"' and trce_stat_proc_dt='"+trce_stat_proc_dt+"' and RMK4='"+kind+"'";
	        System.out.println(selectTableSQL);
	
	        pstmt = conn.prepareStatement(selectTableSQL);
			rs = pstmt.executeQuery();
			rs.next();
			count = rs.getInt("count");
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return count;
	}
}
